/***********************************************************************************************************************************
 * Name:	Trace_Read.sql
 * Author:	Frank Figearo (frank.figearo@us.bbaaviation.com|frank@sqlnerd.me)
 * Summary:	Read a trc file into a table.
 */
USE TempDB;
GO
CREATE TABLE SQLTrace	(
	[TextData]			NVARCHAR(MAX)		Null,
	[BinaryData]		VARBINARY(MAX)		Null,
	[DatabaseID]		INT					Null,
	[TransactionID]		BIGINT				Null,
	[LineNumber]		INT					Null,
	[NTUserName]		NVARCHAR(256)		Null,
	[NTDomainName]		NVARCHAR(256)		Null,
	[HostName]			NVARCHAR(256)		Null,
	[ClientProcessID]	INT					Null,
	[ApplicationName]	NVARCHAR(256)		Null,
	[LoginName]			NVARCHAR(256)		Null,
	[SPID]				INT					Null,
	[Duration]			BIGINT				Null,
	[StartTime]			DATETIME2(3)		Null,
	[EndTime]			DATETIME2(3)		Null,
	[Reads]				BIGINT				Null,
	[Writes]			BIGINT				Null,
	[CPU]				INT					Null,
	[Permissions]		BIGINT				Null,
	[Severity]			INT					Null,
	[EventSubClass]		INT					Null,
	[ObjectID]			INT					Null,
	[Success]			INT					Null,
	[IndexID]			INT					Null,
	[IntegerData]		INT					Null,
	[ServerName]		NVARCHAR(256)		Null,
	[EventClass]		INT					Null,
	[ObjectType]		INT					Null,
	[NestLevel]			INT					Null,
	[State]				INT					Null,
	[Error]				INT					Null,
	[Mode]				INT					Null,
	[Handle]			INT					Null,
	[ObjectName]		NVARCHAR(256)		Null,
	[DatabaseName]		NVARCHAR(256)		Null,
	[FileName]			NVARCHAR(256)		Null,
	[OwnerName]			NVARCHAR(256)		Null,
	[RoleName]			NVARCHAR(256)		Null,
	[TargetUserName]	NVARCHAR(256)		Null,
	[DBUserName]		NVARCHAR(256)		Null,
	[LoginSID]			VARBINARY(MAX)		Null,
	[TargetLoginName]	NVARCHAR(256)		Null,
	[TargetLoginSID]	VARBINARY(MAX)		Null,
	[ColumnPermissions]	INT					Null,
	[LinkedServerName]	NVARCHAR(256)		Null,
	[ProviderName]		NVARCHAR(256)		Null,
	[MethodName]		NVARCHAR(256)		Null,
	[RowCounts]			BIGINT				Null,
	[RequestID]			INT					Null,
	[XactSequence]		BIGINT				Null,
	[EventSequence]		BIGINT				Null,
	[BigintData1]		BIGINT				Null,
	[BigintData2]		BIGINT				Null,
	[GUID]				UNIQUEIDENTIFIER	Null,
	[IntegerData2]		INT					Null,
	[ObjectID2]			BIGINT				Null,
	[Type]				INT					Null,
	[OwnerID]			INT					Null,
	[ParentName]		NVARCHAR(256)		Null,
	[IsSystem]			INT					Null,
	[Offset]			INT					Null,
	[SourceDatabaseID]	INT					Null,
	[SQLHandle]			VARBINARY(MAX)		Null,
	[SessionLoginName]	NVARCHAR(256)		Null,
	[PlanHandle]		VARBINARY(MAX)		Null,
	[GroupID]			INT					Null);
GO
DECLARE @TraceFile NVARCHAR(245)= master.DBAdmin.GetInstancePath(N'WorkingDirectory') + REPLACE(@@SERVERNAME,'\','!') + '_SQLTrace.trc';
INSERT INTO SQLTrace
  SELECT * FROM sys.fn_trace_gettable(@TraceFile, default);
SELECT * FROM SQLTrace;
GO

-- check trace status
DECLARE @property TABLE (PropertyID INT NOT Null PRIMARY KEY CLUSTERED, PropertyText VARCHAR(9) NOT Null);
INSERT INTO @property VALUES (1,'Options'),(2,'File Name'),(3,'Max Size'),(4,'Stop Time'),(5,'Status');
SELECT TraceID, PropertyText AS Property, Value FROM fn_trace_getinfo(default) i INNER JOIN @property p ON i.Property = p.PropertyID;
GO

-- stop the trace
/*
DECLARE @TraceID INT= 0;
EXECUTE sp_trace_setstatus @TraceID, 0;	-- Stop
EXECUTE sp_trace_setstatus @TraceID, 2;	-- Close
 */
 GO
